﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Entities;

namespace DataAccess
{
	public class SmartlistDAL
	{
		private string connectionString = "Data Source=" + new System.IO.DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DoubleFeature").FullName + "\\Resources\\movies.sqlite";

		public bool Save(Smartlist entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO smartlists (smartlist_name, smartlist_criteria) VALUES (?, ?)", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.StringCriteria;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Update(Smartlist entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE smartlists SET smartlist_name=?, smartlist_criteria=? WHERE smartlist_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.StringCriteria;
				SQLiteParameter param3 = new SQLiteParameter();
				param3.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);
				cmd.Parameters.Add(param3);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdateName(Smartlist entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE smartlists SET smartlist_name=? WHERE smartlist_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Delete(Smartlist entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM smartlists WHERE smartlist_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Id;

				conn.Open();

				cmd.Parameters.Add(param1);

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public List<Smartlist> List()
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);
			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM smartlists ORDER BY smartlist_id ASC", conn);

			conn.Open();

			List<Smartlist> collection = new List<Smartlist>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				Smartlist obj = new Smartlist();

				obj.Id = Convert.ToInt32(dr["smartlist_id"]);
				obj.Name = dr["smartlist_name"].ToString();
				obj.StringCriteria = dr["smartlist_criteria"].ToString();

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}
	}
}